import pandas as pd


data = pd.read_excel('韩国数据.xlsx', index_col=0,
                     sheet_name=None)
new_data = {}
for k, v_data in data.items():
    year_data = {}
    for year in range(2019, 2025):
        i_start = (year - 2019) * 3
        i_end = i_start + 3
        temp_data = v_data.iloc[:, i_start:i_end]
        temp_data.columns = temp_data.iloc[0]
        year_data[year] = temp_data.iloc[1:, :]
        
    new_data[k] = year_data


class KoreanData:
    def __init__(self, data, currency='KRW'):
        self.data = new_data
        self.qut_n = [i for i in range(1, 11)]
        self.idxs = self.data['1'][2023].index.to_list()
        self.cols = self.data['1'][2023].columns.to_list()
        self.currency = currency
        self.keep_idxs = self.idxs[:3]
        self.change_idxs = self.idxs[3:]
        self.cur_rate = float(0.005)
    
    def show_some(self):
        print('索引1', self.qut_n)
        print('索引2', range(2019, 2025))
        print('索引3', self.idxs)
        print('索引4', self.cols)

    def transfer_currency(self, df, rows=None):
        if self.currency == 'KRW':
            return df
        if rows is None:
            rows = self.change_idxs
        for idx in rows:
            df.loc[idx,:] = df.loc[idx,:].apply(lambda x: x * self.cur_rate * 12 if isinstance(x, (int, float)) else x)
        return df

    def normalize_by_first_row(self, df):
        first_row = df.iloc[0]
        return df.div(first_row)
    
    def __getitem__(self, name):
        if name in self.qut_n:
            h_dim_data = self.data[str(name)]
            li = []
            for year, data in h_dim_data.items():
                temp_data = data['All households (average)']
                li.append(temp_data)
            df = pd.concat(li, axis=1)
            df.columns = range(2019, 2025)
            return self.transfer_currency(df)
        
        elif name in range(2019, 2025):
            li = []
            for i in self.qut_n:
                se = self.data[str(i)][name]['All households (average)']
                se.name = i
                li.append(se)
            df = pd.concat(li, axis=1)
            return self.transfer_currency(df)
                

k_con = KoreanData(new_data, 'CNY')
k_con.show_some()

k2022 = k_con[2022].iloc[3:,:]
k2022_ratio_all_exp = k_con.normalize_by_first_row(k2022)
k2022_ratio_exp = k2022.div(k2022.iloc[1,:])

